/*
    libhdvbd - Library for building sophisticated DVB applications
    Copyright (C) 2012 Tony Houghton <h@realh.co.uk>

    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU Lesser General Public License as published by
    the Free Software Foundation; either version 3 of the License, or
    (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU Lesser General Public License for more details.

    You should have received a copy of the GNU Lesser General Public License
    along with this program; if not, write to the Free Software
    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
*/

/* Channels database */

#include <glib.h>

#include <stdio.h>

#include "chansdb.h"
#include "codes.h"
#include "dbcommon.h"

static void
create_transports_table(sqlite3 *sql)
{
    hdvb_db_exec(sql,
            "DROP INDEX IF EXISTS transports_ts_ids");
    hdvb_db_exec(sql,
            "DROP TABLE IF EXISTS transports");
    hdvb_db_exec(sql,
            "CREATE TABLE transports ("
            "    ts_id               INT NOT NULL PRIMARY KEY,"
            "    network_id          INT NULL,"
            "    tuning_str          TEXT NOT NULL"
            ")");
    hdvb_db_exec(sql,
            "CREATE INDEX transports_ts_ids ON transports (ts_id)");
}

static void
create_services_table(sqlite3 *sql)
{
    hdvb_db_exec(sql,
            "DROP INDEX IF EXISTS services_lcns");
    hdvb_db_exec(sql,
            "DROP INDEX IF EXISTS services_names");
    hdvb_db_exec(sql,
            "DROP INDEX IF EXISTS services_ts_ids");
    hdvb_db_exec(sql,
            "DROP TABLE IF EXISTS services");
    hdvb_db_exec(sql,
            "CREATE TABLE services ("
            "    service_id          INT NOT NULL,"
            "    ts_id               INT NOT NULL,"
            "    free_ca_mode        INT NOT NULL,"
            "    service_type        INT NOT NULL,"
            "    lcn                 INT NULL,"
            "    service_name        TEXT NULL,"
            "    provider_name       TEXT NULL,"
            "    PRIMARY KEY (service_id, ts_id)"
            ")");
    hdvb_db_exec(sql,
            "CREATE INDEX services_lcns ON services (lcn)");
    hdvb_db_exec(sql,
            "CREATE INDEX services_names ON services (service_name)");
    hdvb_db_exec(sql,
            "CREATE INDEX services_ts_ids ON services (ts_id)");
}

static void
create_lcns_table(sqlite3 *sql)
{
    /* section_id is bouquet_id for Freesat, network_id for Freeview. For
     * Freeview this is pretty much irrelevant because there's a one-to-one
     * relationship between lcn and service_id.
     */
    hdvb_db_exec(sql,
            "CREATE TABLE lcns ("
            "    service_id          INT NOT NULL,"
            "    section_id          INT NOT NULL,"
            "    region              INT NOT NULL,"
            "    lcn                 INT NOT NULL,"
            "    UNIQUE (service_id, region, lcn)"
            ")");
}

static void
create_dvbt2_ids_table(sqlite3 *sql)
{
    hdvb_db_exec(sql,
            "CREATE TABLE dvbt2_ids ("
            "    ts_id               INT NOT NULL,"
            "    plp_id              INT NOT NULL,"
            "    system_id           INT NOT NULL,"
            "    PRIMARY KEY (ts_id)"
            ")");
}

#if 0

/* region_code is made up of primary (MSB), secondary and tertiary (LSB)
 * region codes combined into one word. Disused region fields have all bits
 * set.
 */

static void
create_service_regions_table(sqlite3 *sql)
{
    hdvb_db_exec(sql,
            "CREATE TABLE service_regions ("
            "    service_id          INT NOT NULL,"
            "    country_code        INT NOT NULL,"
            "    region_depth        INT NOT NULL,"
            "    region_code         INT NOT NULL"
            ")");
}

static void
create_region_names_table(sqlite3 *sql)
{
    hdvb_db_exec(sql,
            "CREATE TABLE region_names ("
            "    country_code        INT NOT NULL,"
            "    lang                INT NOT NULL,"
            "    region_depth        INT NOT NULL,"
            "    region_code         INT NOT NULL,"
            "    name                TEXT NULL"
            ")");
}

#endif

static void
create_pids_table(sqlite3 *sql)
{
    hdvb_db_exec(sql,
            "CREATE TABLE pids ("
            "    service_id          INT NOT NULL,"
            "    ts_id               INT NOT NULL,"
            "    pid                 INT NOT NULL,"
            "    stream_type         INT NOT NULL,"
            "    lang                INT NOT NULL,"
            "    audio_type          INT"
            ")");
}

void
hdvb_chans_db_create(sqlite3 *sql, gpointer handle)
{
    create_transports_table(sql);
    create_services_table(sql);
    create_lcns_table(sql);
    create_dvbt2_ids_table(sql);
#if 0
    create_service_regions_table(sql);
    create_region_names_table(sql);
#endif
    hdvb_db_exec(sql, "CREATE TABLE valid (valid INT NOT NULL DEFAULT 0);");
    hdvb_db_exec(sql, "INSERT OR REPLACE INTO valid (valid) VALUES (0);");
    if (GPOINTER_TO_INT(handle))
        create_pids_table(sql);
}

sqlite3 *
hdvb_chans_db_open(const char *filename, HdvbDbCreator creator,
        gboolean with_pids)
{
    if (!creator)
        creator = hdvb_chans_db_create;
    return hdvb_db_open(filename, HDVB_CHANSDB_VERSION, creator,
            GINT_TO_POINTER(with_pids));
}

sqlite3_stmt *
hdvb_chans_db_statement_insert_service(sqlite3 *sql)
{
    return hdvb_db_prepare_statement(sql,
            "INSERT OR REPLACE INTO services "
            "   (service_id, ts_id, free_ca_mode, service_type, "
            "   lcn, service_name, provider_name) "
            "   VALUES (?, ?, ?, ?, ?, ?, ?)");
}

sqlite3_stmt *
hdvb_chans_db_statement_insert_transport(sqlite3 *sql)
{
    return hdvb_db_prepare_statement(sql,
            "INSERT OR REPLACE INTO transports "
            "   (ts_id, network_id, tuning_str) "
            "   VALUES (?, ?, ?)");
}

sqlite3_stmt *
hdvb_chans_db_statement_insert_lcn(sqlite3 *sql)
{
    return hdvb_db_prepare_statement(sql,
            "INSERT OR REPLACE INTO lcns "
            "   (service_id, section_id, region, lcn) "
            "   VALUES (?, ?, ?, ?)");
}

sqlite3_stmt *
hdvb_chans_db_statement_insert_dvbt2_ids(sqlite3 *sql)
{
    return hdvb_db_prepare_statement(sql,
            "INSERT OR REPLACE INTO dvbt2_ids "
            "   (ts_id, plp_id, system_id)"
            "   VALUES (?, ?, ?)");
}

#if 0
sqlite3_stmt *
hdvb_chans_db_statement_insert_service_region(sqlite3 *sql)
{
    return hdvb_db_prepare_statement(sql,
            "INSERT OR REPLACE INTO service_regions "
            "   (service_id, country_code, region_depth, region_code) "
            "   VALUES (?, ?, ?, ?)");
}

sqlite3_stmt *
hdvb_chans_db_statement_insert_region_name(sqlite3 *sql)
{
    return hdvb_db_prepare_statement(sql,
            "INSERT OR REPLACE INTO service_regions "
            "   (service_id, country_code, region_depth, region_code) "
            "   VALUES (?, ?, ?, ?)");
}
#endif

sqlite3_stmt *
hdvb_chans_db_statement_lookup_service_by_name(sqlite3 *sql)
{
    return hdvb_db_prepare_statement(sql,
            "SELECT services.*, transports.* FROM services, transports WHERE "
            "service_name = ? AND services.ts_id = transports.ts_id");
}

static char *
get_where_clause(gboolean include_scrambled, guint type_mask)
{
    char *clause = NULL;
    char *old;

    if (type_mask != HDVB_CHANS_DB_TYPE_ALL)
    {
        if (type_mask & HDVB_CHANS_DB_TYPE_SDTV)
        {
            clause = g_strdup_printf("service_type IN (%d, %d",
                    HDVB_SERVICE_TYPE_MPEG2_SDTV,
                    HDVB_SERVICE_TYPE_ADVANCED_SDTV);
        }
        if (type_mask & HDVB_CHANS_DB_TYPE_HDTV)
        {
            old = clause;
            clause = g_strdup_printf("%s%s%d, %d",
                    old ? old : "service_type IN (",
                    old ? ", " : "",
                    HDVB_SERVICE_TYPE_MPEG2_HDTV,
                    HDVB_SERVICE_TYPE_ADVANCED_HDTV);
            g_free(old);
        }
        if (type_mask & HDVB_CHANS_DB_TYPE_HDTV)
        {
            old = clause;
            clause = g_strdup_printf("%s%s%d, %d",
                    old ? old : "service_type IN (",
                    old ? ", " : "",
                    HDVB_SERVICE_TYPE_MPEG2_HDTV,
                    HDVB_SERVICE_TYPE_ADVANCED_HDTV);
            g_free(old);
        }
        if (type_mask & HDVB_CHANS_DB_TYPE_RADIO)
        {
            old = clause;
            clause = g_strdup_printf("%s%s%d, %d",
                    old ? old : "service_type IN (",
                    old ? ", " : "",
                    HDVB_SERVICE_TYPE_MPEG2_RADIO,
                    HDVB_SERVICE_TYPE_ADVANCED_RADIO);
            g_free(old);
        }
        if (type_mask & HDVB_CHANS_DB_TYPE_TELETEXT)
        {
            old = clause;
            clause = g_strdup_printf("%s%s%d, %d",
                    old ? old : "service_type IN (",
                    old ? ", " : "",
                    HDVB_SERVICE_TYPE_TELETEXT, HDVB_SERVICE_TYPE_DATA);
            g_free(old);
        }
        old = clause;
        clause = g_strdup_printf("%s)", clause);
        g_free(old);
    }

    if (!include_scrambled)
    {
        old = clause;
        clause = g_strdup_printf("%s %s free_ca_mode = 0",
            old ? old : "", old ? "AND" : "");
        g_free(old);
    }

    return clause;
}

static char *
get_order_clause(const char *order_by)
{
    if (order_by && order_by[0])
    {
        return g_strdup_printf("ORDER BY %s", order_by);
    }
    return NULL;
}

sqlite3_stmt *
hdvb_chans_db_statement_get_services_brief(sqlite3 *sql,
        const char *order_by, gboolean include_scrambled, guint type_mask)
{
    char *where = get_where_clause(include_scrambled, type_mask);
    char *order = get_order_clause(order_by);
    char *stmt_s = g_strdup_printf(
            "SELECT lcn, service_id, service_name, free_ca_mode, service_type "
            "FROM services %s %s %s",
            where ? "WHERE" : "",
            where ? where : "", order ? order : "");
    sqlite3_stmt *stmt = hdvb_db_prepare_statement(sql, stmt_s);

    g_free(stmt_s);
    g_free(order);
    g_free(where);
    return stmt;
}

sqlite3_stmt *
hdvb_chans_db_statement_get_services_with_tuning(sqlite3 *sql,
        const char *order_by, gboolean include_scrambled, guint type_mask)
{
    char *where = get_where_clause(include_scrambled, type_mask);
    char *order = get_order_clause(order_by);
    char *stmt_s = g_strdup_printf(
            "SELECT lcn, services.service_id, service_name, "
            "   free_ca_mode, service_type, transports.ts_id, tuning_str "
            "FROM services, transports "
            "WHERE services.ts_id = transports.ts_id %s "
            "%s %s",
            where ? "AND" : "",
            where ? where : "", order ? order : "");
    sqlite3_stmt *stmt = hdvb_db_prepare_statement(sql, stmt_s);

    g_free(stmt_s);
    g_free(order);
    g_free(where);
    return stmt;
}

gboolean
hdvb_chans_db_get_is_valid(sqlite3 *sql)
{
    sqlite3_stmt *stmt = hdvb_db_prepare_statement(sql,
            "SELECT valid FROM valid");
    gboolean result = FALSE;

    if (stmt)
    {
        if (sqlite3_step(stmt) == SQLITE_ROW)
            result = sqlite3_column_int(stmt, 0) == 1;
        sqlite3_finalize(stmt);
    }
    return result;
}

void
hdvb_chans_db_set_is_valid(sqlite3 *sql, gboolean valid)
{
    /* Bad practice, but far easier than binding, and safe to do here */
    char *stmt = g_strdup_printf("UPDATE OR REPLACE valid SET valid = %d",
            valid);
    hdvb_db_exec(sql, stmt);
    g_free(stmt);
}

sqlite3_stmt *
hdvb_chans_db_statement_lookup_tuning_for_ts_id(sqlite3 *sql)
{
    return hdvb_db_prepare_statement(sql,
            "SELECT tuning_str FROM transports WHERE ts_id = ?");
}

sqlite3_stmt *
hdvb_chans_db_statement_update_tuning_for_ts_id(sqlite3 *sql)
{
    return hdvb_db_prepare_statement(sql,
            "UPDATE transports SET tuning_str = ? WHERE ts_id = ?");
}

sqlite3_stmt *
hdvb_chans_db_statement_lookup_tuning_for_service_id(sqlite3 *sql)
{
    return hdvb_db_prepare_statement(sql,
            "SELECT services.ts_id, tuning_str FROM services, transports"
            "   WHERE service_id = ? AND services.ts_id = transports.ts_id");
}
